using System;
using System.Collections;
using System.Collections.Generic;
using Framework.Library.Core.Config;
using Framework.Library.Core.Data.DataEngine;
using Framework.Library.Data.DAL;
using Framework.Library.Data.IDAO;
using Framework.Library.Core.Utils.Database;
using System.Data;
using System.Text;

namespace Framework.Library.Data.DAO.SqlServer
{
    [Serializable]
    internal class PortalMenuManager : IPortalMenuManager
    {
        private Configurations cfg = new Configurations();
        #region IPortalMenuManager Members

        public List<PortalMenu> GetAll(int PortalID)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            builder.AddCondition("PORTALID", PortalID);
            builder.AddSortColumn("PRIORITY", SortOrder.Ascending);
            return GeneralConnection.ExecuteList<PortalMenu>(CommandType.Text, builder);
        }
        
        public void Delete(int _iD)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            StringBuilder sb = new StringBuilder();
            sb.Append("BEGIN\t");
            sb.Append("with CTE(ID, PARENTID, NAME) as (\t");
            sb.Append("select ID, PARENTID, NAME from " + Configurations.DATABASEOWNER + "PORTAL_MENU c where c.ID = @ID\t");
            sb.Append("union all\t");
            sb.Append("select c.ID, c.PARENTID, c.NAME from " + Configurations.DATABASEOWNER + "PORTAL_MENU c inner join CTE ch on c.PARENTID = ch.ID\t");
            sb.Append(")\t");
            sb.Append("delete " + Configurations.DATABASEOWNER + "PORTAL_MENU WHERE ID in (select ID from CTE)\t");
            sb.Append("delete " + Configurations.DATABASEOWNER + "PORTAL_SECTIONS WHERE MEID not in (select ID from " + Configurations.DATABASEOWNER + "PORTAL_MENU) AND MEID <> 0\t");
            sb.Append("END\t");
            builder.Query = sb.ToString();
            builder.AddParameter("ID", _iD);
            builder.QueryType = QueryTypes.TSql;
            GeneralConnection.ExecuteNonQuery(CommandType.Text, builder);
        }

        public List<PortalMenu> GetByCategory(int PortalID, int MenuID)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            builder.AddCondition("PORTALID", PortalID);
            builder.AddCondition("PARENTID", MenuID);
            builder.AddSortColumn("PRIORITY", SortOrder.Ascending);

            return GeneralConnection.ExecuteList<PortalMenu>(CommandType.Text, builder);
        }

        public List<PortalMenu> GetTopMenuHierarchism(int MenuID)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            StringBuilder sb = new StringBuilder();
            sb.Append("with Hierachy as (\t");
            sb.Append("select * from " + Configurations.DATABASEOWNER + "PORTAL_MENU c where c.ID = @ID\t");
            sb.Append("union all\t");
            sb.Append("select c.* from " + Configurations.DATABASEOWNER + "PORTAL_MENU c inner join Hierachy ch on c.PARENTID = ch.ID)\t");
            sb.Append("select * from Hierachy\t");
            builder.Query = sb.ToString();
            builder.AddParameter("ID", MenuID);
            builder.QueryType = QueryTypes.TSql;
            return GeneralConnection.ExecuteList<PortalMenu>(CommandType.Text, builder);
        }

        public PortalMenu GetInfo(int PortalID, int ID)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            builder.AddCondition("PORTALID", PortalID);
            if(ID > 0)
                builder.AddCondition("ID ", ID);
            else
                builder.AddCondition("HOMEPAGE", 1);
            List<PortalMenu> tab = GeneralConnection.ExecuteList<PortalMenu>(CommandType.Text, builder);
            if (tab.Count > 0)
                return tab[0];
            return null;
        }

        public List<PortalMenu> GetAll(int PortalID, int UserId)
        {
            QueryBuilder builder = new QueryBuilder("PORTAL_MENU");
            StringBuilder sb = new StringBuilder();
            sb.Append("DECLARE @SQL nvarchar(max)\t");
            sb.Append("DECLARE @MENU nvarchar(500) \t");
            sb.Append("SET @SQL = 'SELECT * FROM " + Configurations.DATABASEOWNER + "PORTAL_MENU WHERE'\t");
            sb.Append("SELECT @MENU = (select " + Configurations.DATABASEOWNER + "PORTAL_ROLES.PERMISIONS from " + Configurations.DATABASEOWNER + "PORTAL_USERSROLES inner join " + Configurations.DATABASEOWNER + "PORTAL_ROLES ON NS_PORTAL_USERSROLES.ROLEID = NS_PORTAL_ROLES.ROLEID WHERE NS_PORTAL_USERSROLES.USERID = @USERID and NS_PORTAL_ROLES.PORTALID = @PORTALID)\t");
            sb.Append("IF CHARINDEX(@MENU, '0', 1) > 0\t");
            sb.Append("BEGIN\t");
            sb.Append("SET @SQL = @SQL + ' PORTALID = ' + CONVERT(nvarchar(10), @PORTALID)\t");
            sb.Append("END\t");
            sb.Append("ELSE\t");
            sb.Append("BEGIN\t");
            sb.Append("SET @SQL = @SQL + ' ID in (' + @MENU + ')'  \t");
            sb.Append("END\t");
            sb.Append("EXEC sp_executesql @SQL");
            builder.Query = sb.ToString();
            builder.AddParameter("PORTALID", PortalID);
            builder.AddParameter("USERID", UserId);
            builder.QueryType = QueryTypes.TSql;
            return GeneralConnection.ExecuteList<PortalMenu>(CommandType.Text, builder);
        }
        #endregion
    }
}